package cn.edu.lingnan.dao;

import cn.edu.lingnan.pojo.Student;
import cn.edu.lingnan.util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class StudentDaoMysqlImpl implements StudentDao {
    @Override
    public boolean newStudent(Student _student) {
        boolean flag=false;
        Connection conn = null;
        PreparedStatement prep = null;
        try {
            conn = DBUtil.getInstance().getConnection();
            prep = conn.prepareStatement("insert into student values(?,?,?,?,?)");
            prep.setString(1,_student.getSid());
            prep.setString(2,_student.getSname());
            prep.setString(3,_student.getPassword());
            prep.setString(4, _student.getSright());
            prep.setString(5, _student.getSflag());
            prep.executeUpdate();
            flag=true;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.getInstance().close(prep,conn);
        }
        return flag;
    }


    @Override
    public Student findStudentBySidAndPassword(String _sid, String _password) {
        boolean flag=false;
        Connection conn = null;
        PreparedStatement prep = null;
        ResultSet rs=null;
        Student student=null;
        try {
            conn = DBUtil.getInstance().getConnection();
            prep = conn.prepareStatement("select * from student where sid=? AND password=?");
            prep.setString(1,_sid);
            prep.setString(2,_password);
            rs= prep.executeQuery();
            if(rs.next()){
                student=new Student();
                student.setSid(rs.getString("sid"));
                student.setSname(rs.getString("sname"));
                student.setPassword(rs.getString("password"));
                student.setSright(rs.getString("sright"));
                student.setSflag(rs.getString("sflag"));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.getInstance().close(prep,conn);
        }
        return student;
    }


    @Override
    public List<Student> findStudnetAll() {
        List<Student> list = new ArrayList<>();
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getInstance().getConnection();
            st = conn.createStatement();
            rs = st.executeQuery("select * from student");
            while (rs.next()) {
                Student student = new Student();
                student.setSid(rs.getString(1));
                student.setSname(rs.getString(2));
                student.setPassword(rs.getString(3));
                student.setSright(rs.getString(4));
                student.setSflag(rs.getString(5));
                list.add(student);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.getInstance().close(rs,st,conn);
        }
        return list;
    }


    public Student findStudentBySid(String _sid) {
        Student student = new Student();
        Connection conn = null;
        PreparedStatement prep = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getInstance().getConnection();
            prep = conn.prepareStatement("select * from student where sid=?");
            prep.setString(1,_sid);
            rs = prep.executeQuery();
            while (rs.next()) {
                student.setSid(rs.getString(1));
                student.setSname(rs.getString(2));
                student.setPassword(rs.getString(3));
                student.setSright(rs.getString(4));
                student.setSflag(rs.getString(5));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.getInstance().close(rs,prep,conn);
        }
        return student;
    }


    @Override
    public List<Student> findStudentBySname(String _sname) {
        List<Student> list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement prep = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getInstance().getConnection();
            prep = conn.prepareStatement("select * from student where sname like ?");
            prep.setString(1, "%" + _sname + "%");
            rs = prep.executeQuery();
            while (rs.next()) {
                Student student = new Student();
                student.setSid(rs.getString(1));
                student.setSname(rs.getString(2));
                student.setPassword(rs.getString(3));
                student.setSright(rs.getString(4));
                student.setSflag(rs.getString(5));
                list.add(student);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.getInstance().close(rs,prep,conn);
        }
        return list;
    }


    @Override
    public boolean updateNameBySid(String _sid, String _sname) {
        boolean flag=false;
        Connection conn = null;
        PreparedStatement prep = null;
        try {
            conn = DBUtil.getInstance().getConnection();
            prep = conn.prepareStatement("update student set sname=? where sid=?");
            prep.setString(1,_sname);
            prep.setString(2,_sid);
            if(prep.executeUpdate()==1){
                flag=true;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.getInstance().close(prep,conn);
        }
        return flag;
    }


    @Override
    public boolean updatePasswordBySid(String _sid, String _password) {
        boolean flag=false;
        Connection conn = null;
        PreparedStatement prep = null;
        try {
            conn = DBUtil.getInstance().getConnection();
            prep = conn.prepareStatement("update student set password=? where sid=?");
            prep.setString(1,_password);
            prep.setString(2,_sid);
            prep.executeUpdate();
            flag=true;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.getInstance().close(prep,conn);
        }
        return flag;
    }


    @Override
    public boolean updateSrightBySid(String _sid, String _sright) {
        boolean flag=false;
        Connection conn = null;
        PreparedStatement prep = null;
        try {
            conn = DBUtil.getInstance().getConnection();
            prep = conn.prepareStatement("update student set sright=? where sid=?");
            prep.setString(1,_sright);
            prep.setString(2,_sid);
            prep.executeUpdate();
            flag=true;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.getInstance().close(prep,conn);
        }
        return flag;
    }


    @Override
    public boolean updateSflagBySid(String _sid, String _sflag) {
        boolean flag=false;
        Connection conn = null;
        PreparedStatement prep = null;
        try {
            conn = DBUtil.getInstance().getConnection();
            prep = conn.prepareStatement("update student set sflag=? where sid=?");
            prep.setString(1,_sflag);
            prep.setString(2,_sid);
            prep.executeUpdate();
            flag=true;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.getInstance().close(prep,conn);
        }
        return flag;
    }


    @Override
    public boolean updateStudent(Student _student) {
        boolean flag=false;
        Connection conn = null;
        PreparedStatement prep = null;
        try {
            conn = DBUtil.getInstance().getConnection();
            prep = conn.prepareStatement("update student set sname=?,password=?,sright=?,sflag=?" +
                    " where sid=?");
            prep.setString(1,_student.getSname());
            prep.setString(2,_student.getPassword());
            prep.setString(3,_student.getSright());
            prep.setString(4,_student.getSflag());
            prep.setString(5,_student.getSid());
            prep.executeUpdate();
            flag=true;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.getInstance().close(prep,conn);
        }
        return flag;
    }


    @Override
    public boolean deleteStudentBySid(String _sid) {
        boolean flag=false;
        Connection conn = null;
        PreparedStatement prep = null;
        try {
            conn = DBUtil.getInstance().getConnection();
            prep = conn.prepareStatement("delete from student where sid =?");
            prep.setString(1,_sid);
            if(prep.executeUpdate()==1){
                flag=true;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.getInstance().close(prep,conn);
        }
        return flag;
    }
}
